package com.glgc.projecti.common;

import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.jolbox.bonecp.BoneCPDataSource;

public class DBService {
	
	public final static String MYSQL = "mysql";
	public final static String MSSQL = "sqlserver";
	public final static String ORACLE = "oracle";

	public static Map<String, DataSource> datasources = new HashMap<String, DataSource>();
	
	public synchronized static DataSource getDataSource(String name) throws Exception{
		if(!datasources.containsKey(name)) throw new Exception("数据源"+name+"没有初始化") ;
		return datasources.get(name);
	}
	
	public synchronized static DataSource createDataSource(String name, String type,
			String username, String password, String url, String driverstr,
			String testsql) throws Exception {
		if(datasources.containsKey(name))return datasources.get(name);
		
		switch (type) {
		case "mysql":
			//jdbc:mysql://ip:port/yourdb
			Class.forName("com.mysql.jdbc.Driver");
			break;
		case "oracle":
			//jdbc:oracle:thin:@ip:prot:orcl
			Class.forName("oracle.jdbc.driver.OracleDriver");
			break;
		case "sqlserver":
			//jdbc:sqlserver://ip:port;databaseName=dbname
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			break;
		default:
			Class.forName(driverstr);
			break;
		}
		if (testsql == null)
			testsql = "select 1 dual";
		//BasicDataSource ds = new XBasicDataSource();
		BoneCPDataSource ds = new BoneCPDataSource();
		ds.setLogStatementsEnabled(true);
		ds.setJdbcUrl(url);
		ds.setUsername(username);
		ds.setPassword(password);
		ds.setMinConnectionsPerPartition(3);
		ds.setMaxConnectionsPerPartition(50);
		ds.setAcquireIncrement(2);
		ds.setAcquireRetryAttempts(5);
		ds.setCloseConnectionWatch(false);
		ds.setConnectionTestStatement(testsql);
		ds.setIdleConnectionTestPeriodInSeconds(60 * 60 * 2);
		ds.setIdleMaxAgeInSeconds(60 * 60);
		ds.setPartitionCount(1);
		 
		datasources.put(name, ds);
		return ds;
	}

	public static void main(String[] args)  throws Exception 	{	
		QueryRunner _run = new QueryRunner(DBService.createDataSource(
				"mysql",
				DBService.MYSQL,
				ResourceBundle.getBundle("config").getString(
						"username"),
				ResourceBundle.getBundle("config").getString(
						"password"),
				ResourceBundle.getBundle("config").getString(
								"dbserver-url"), null,
				null));
		
		String sql = "select * from pigcms_glzq_account a where a.wecha_id = 2121";

		List<Map<String, Object>> list = _run.query(sql, new MapListHandler());
		for (int i=0 ; i< list.size();i++){
			System.out.println(list.get(i));
		} 
		
	
		
		
		if(true)return ;

		try {
			
			  sql  = "select top 10 a.ID,a.InfoTitle,convert(VARCHAR(10),a.InfoPublDate,120) as InfoPublDate ,substring(a.Content,1,200)+'....' as content from NI_News a where a.Category=12111  or 1=(select count(1) from NI_News) ";
			QueryRunner run = new QueryRunner(createDataSource("mssql10",DBService.MSSQL,"jydb","jydb","jdbc:sqlserver://10.192.0.192:1433;databaseName=jydb",null,null));
			List<Map<String, Object>> m = run.query(sql,
					new MapListHandler());
			System.out.println(m);
			
			
			
			
			
			if(true)return ;
			run = new QueryRunner(createDataSource("mysql127",DBService.MYSQL,"root","","jdbc:mysql://127.0.0.1:3306/p2p?useUnicode=true&amp;characterEncoding=UTF-8",null,null));
			m = run.query("SELECT 1",
					new MapListHandler());
			System.out.println(m);
			
			Connection conn = getDataSource("mysql127").getConnection();
//			conn.setAutoCommit(false);
			run = new QueryRunner();
			m =run.query(conn, "SELECT 2", new MapListHandler());
			System.out.println(m);
			DbUtils.close(conn);
			
			run = new QueryRunner(createDataSource("oracle126",DBService.ORACLE,"hs_data","hundsun","jdbc:oracle:thin:@10.197.0.126:1521:orcl",null,null));
			m =run.query("SELECT 3 from dual ", new MapListHandler());
			System.out.println(m);
			
			
			long start = System.currentTimeMillis();
			for(int i = 0 ;i<500;i++){
				
				Runnable r = new Runnable() {
					
					@Override
					public void run() {
						// TODO Auto-generated method stub
						try {
							QueryRunner run =new QueryRunner(DBService.createDataSource("oracle126",DBService.ORACLE,"hs_data","hundsun","jdbc:oracle:thin:@10.197.0.126:1521:orcl",null,null));
							List<Map<String, Object>> m =run.query("SELECT '"+this.toString()+"' from dual ", new MapListHandler());
							System.out.println(m);
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				};
				Thread t = new Thread(r,"xuhh_"+i);
				
				t.start();
				t.join();
				
				
			}
			System.out.println(System.currentTimeMillis()-start);
			
		
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			 
		}
		
		

	}
	
	
	

}
